package model;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

public class DBHandler {

    private Statement stmt;
    private ResultSet rs;
    private DBConnector dbc;

    public DBHandler(DBConnector dbc) {
        this.dbc = dbc;
        stmt = dbc.getStmt();
    }

    public Ordre fetchOrdre(int ordreId) throws SQLException {
        Ordre o = null;

        String sql = "SELECT ordre.*, pizza.navn, pizzaOrdre.*,  pizzaSize.size, pizzaSize.pris from pizzaOrdre"
                + " LEFT JOIN ordre ON ordre.ordreId = pizzaOrdre.ordreId"
                + " LEFT JOIN pizza ON pizza.pizzaId = pizzaOrdre.pizzaId"
                + " LEFT JOIN pizzaSize ON pizza.pizzaId = pizzaOrdre.pizzaId"
                + " WHERE ordre.ordreId = '" + ordreId + "' AND pizzaSize.sizeId = pizzaOrdre.pizzaSizeId";

        rs = stmt.executeQuery(sql);

        int c = 0;
        while (rs.next()) {
            if (c == 0) {
                o = new Ordre(rs.getInt("ordre.ordreId"), rs.getInt("ordre.status"), rs.getInt("ordre.kundeId"));
                o.setBudId(rs.getInt("ordre.budId"));
                o.setBestillingDatoTid(rs.getString("ordre.bestillingsDato"));
                o.setLeveringsDatoTid(rs.getString("ordre.leveringsDato"));
                c++;
            }
            o.addPizza(new PizzaOrdre(new Pizza(rs.getInt("pizzaOrdre.pizzaId"), rs.getString("pizza.navn")), rs.getInt("pizzaSize.size"), rs.getInt("pizzaOrdre.antal")));
        }

        return o;
    }

    public void setOrdreStatus(Ordre o) throws SQLException {
        String sql = "UPDATE ordre SET status='" + o.getStatus() + "' WHERE ordreId='" + o.getOrdreId() + "'";
        stmt.execute(sql);
    }

    public void addPizza(Pizza p) throws SQLException {
        // Indsæt pizza i tabel.
        String sql = "insert into pizza (navn) values('" + p.getNavn() + "')";
        stmt.execute(sql);

        // Indsæt PizzaSizes i tabel.
        ArrayList<PizzaSize> ps = p.getSizes();
        for (int i = 0; i < ps.size(); i++) {
            sql = "insert into pizzaSize (size, pris, pizzaId) values(" + ps.get(i).getSize() + "," + ps.get(i).getPris() + "," + p.getPizzaId() + ")";
            stmt.execute(sql);
        }

        // Indsæt PizzaToppings i tabel.
        ArrayList<Topping> tl = p.getToppings();
        for (int i = 0; i < tl.size(); i++) {
            sql = "insert into pizzaTopping (pizzaId,toppingId) values(" + p.getPizzaId() + "," + tl.get(i).getToppingId() + ")";
            stmt.execute(sql);
        }
    }

    public void editPizza(Pizza p) throws SQLException {
        int pizzaId = p.getPizzaId();
        String sql = "delete from pizzaTopping where pizzaId = " + pizzaId;
        stmt.execute(sql);
        sql = "delete from pizzaSize where pizzaId = " + pizzaId;
        stmt.execute(sql);
        sql = "delete from pizza where pizzaId = " + pizzaId;
        stmt.execute(sql);
        addPizza(p);
    }

    public void deletePizza(Pizza p) throws SQLException {
        int pizzaId = p.getPizzaId();
        String sql = "delete from pizzaTopping where pizzaId = " + pizzaId;
        stmt.execute(sql);
        sql = "delete from pizzaSize where pizzaId = " + pizzaId;
        stmt.execute(sql);
        sql = "delete from pizza where pizzaId = " + pizzaId;
        stmt.execute(sql);
    }

    // Hent pizza med det specificerede pizzaId.
    public Pizza getPizza(int pizzaId) throws SQLException {
        Pizza p = null;
        ResultSet rSet;

        String sql = "select * from pizza where pizzaId = " + pizzaId + " LIMIT 1";
        rs = stmt.executeQuery(sql);

        while (rs.next()) {

            // Opret Pizza'en.
            p = new Pizza(rs.getInt("pizzaId"), rs.getString("navn"));

            // Find alle størrelser til denne Pizza.
            sql = "select * from pizzaSize where pizzaId = " + rs.getInt("pizzaId");
            rSet = stmt.executeQuery(sql);
            while (rSet.next()) {
                p.addSize(rSet.getInt("size"), rSet.getInt("pris"));
            }

            // Find alle Topping's til denne Pizza.
            sql = "select * from pizzaTopping where pizzaId = " + rs.getInt("pizzaId");
            rSet = stmt.executeQuery(sql);
            while (rSet.next()) {
                sql = "select * from topping where toppingId = " + rSet.getInt("toppingId");
                ResultSet res = stmt.executeQuery(sql);
                while (res.next()) {
                    Topping t = new Topping(res.getInt("toppingId"), res.getString("navn"));
                    p.addTopping(t);
                }
            }
        }
        return p;
    }

    // Hent en liste over alle Pizza'er, med både størrelser og toppings.
    public ArrayList<Pizza> getPizzaList() throws SQLException {
        ArrayList<Pizza> pl = new ArrayList();
        ResultSet rSet;

        // Find alle Pizza'er.
        String sql = "SELECT * FROM pizza";
        rs = stmt.executeQuery(sql);
        while (rs.next()) {
            pl.add(new Pizza(rs.getInt("pizzaId"), rs.getString("navn")));
        }
        for (int i = 0; i < pl.size(); i++) {
            sql = "SELECT * FROM pizzaSize WHERE pizzaId = '" + pl.get(i).getPizzaId() + "'";
            rs = stmt.executeQuery(sql);
            while (rs.next()) {
                pl.get(i).addSize(rs.getInt("size"), rs.getInt("pris"));
            }
            sql = "SELECT pizzaTopping.toppingId, topping.navn FROM pizzaTopping"
                    + " LEFT JOIN topping ON topping.toppingId = pizzaTopping.toppingId"
                    + " WHERE pizzaTopping.pizzaId = '" + pl.get(i).getPizzaId() + "'";
            rs = stmt.executeQuery(sql);
            while (rs.next()) {
                pl.get(i).addTopping(new Topping(rs.getInt("pizzaTopping.toppingId"), rs.getString("topping.navn")));
            }
        }
        return pl;
    }

    public boolean doesPizzaExist(String navn) throws SQLException {
        boolean result = false;
        String sql = "SELECT * from pizza WHERE navn = '" + navn + "'";
        rs = stmt.executeQuery(sql);
        if (rs.next()) {
            result = true;
        }
        return result;
    }

    public boolean doesKundeExist(String fornavn, String efternavn) throws SQLException {
        boolean result = false;
        String sql = "SELECT * from kunde WHERE fornavn = '" + fornavn + "' and efternavn = '" + efternavn + "'";
        rs = stmt.executeQuery(sql);
        if (rs.next()) {
            result = true;
        }
        return result;
    }

    public boolean doesBudExist(String fnavn, String enavn) throws SQLException {
        boolean result = false;
        String sql = "SELECT * from bud WHERE fornavn = '" + fnavn + "' AND efternavn = '" + enavn + "'";
        rs = stmt.executeQuery(sql);
        if (rs.next()) {
            result = true;
        }
        return result;
    }

    public boolean doesToppingExist(String navn) throws SQLException {
        boolean result = false;
        String sql = "SELECT * from topping WHERE navn = '" + navn + "'";
        rs = stmt.executeQuery(sql);
        if (rs.next()) {
            result = true;
        }
        return result;
    }

    public void addTopping(Topping topping) throws SQLException {
        String sql = "INSERT INTO Topping (navn) VALUES('" + topping.getNavn() + "')";
        stmt.execute(sql);
    }

    public void editTopping(String navn, String nytNavn) throws SQLException {
        String sql = "UPDATE Topping SET navn = '" + nytNavn + "' WHERE navn = '" + navn + "'";
        stmt.execute(sql);
    }

    public void deleteTopping(String navn) throws SQLException {
        String sql = "DELETE FROM Topping WHERE navn = '" + navn + "'";
        stmt.execute(sql);
    }

    public void addKunde(Kunde kunde) {
        String forNavn = kunde.getForNavn();
        String efterNavn = kunde.getEfterNavn();
        int tlf = kunde.getTlf();
        String adr = kunde.getKundeAdresse();
        String by = kunde.getBy();
        int postnr = kunde.getPostNr();

        String sql = "insert into Kunde (fornavn, efternavn, adresse, bynavn, postnr) values('" + forNavn + "', '" + efterNavn + "', '" + adr + "', '" + by + "', '" + postnr + "')";
        String sqlGetKundeId = "Select kundeId from Kunde ORDER BY kundeId desc LIMIT 1";

        try {
            stmt.execute(sql);

            rs = stmt.executeQuery(sqlGetKundeId);
            while (rs.next()) {
                int kundeId = rs.getInt("kundeId");
                String sqlTlf = "insert into tlfnr values('" + kundeId + "', '" + tlf + "')";
                stmt.execute(sqlTlf);
            }

        } catch (SQLException e) {
            System.out.println(e.getMessage());
        }
    }

    public Kunde getKunde(int kundeId) throws SQLException {
        Kunde kunde = null;
        String sql = "SELECT kunde.*, tlfnr.tlf, postnrbynavn.bynavn FROM kunde"
                + " LEFT JOIN tlfnr ON tlfnr.kundeId = kunde.kundeId"
                + " LEFT JOIN postnrbynavn ON postnrbynavn.postnr = kunde.postnr"
                + " WHERE kunde.kundeId = '" + kundeId + "'";
        rs = stmt.executeQuery(sql);
        while (rs.next()) {
            kunde = new Kunde(rs.getString("kunde.fornavn"), rs.getString("kunde.efternavn"), rs.getString("kunde.adresse"), rs.getInt("kunde.postnr"), rs.getString("postnrbynavn.bynavn"), rs.getInt("tlfnr.tlf"));
            kunde.setKundeId(rs.getInt("kunde.kundeId"));
        }
        return kunde;
    }

    public void editKunde(Kunde kunde) {
        String fornavn = kunde.getForNavn();
        String efternavn = kunde.getEfterNavn();
        int tlf = kunde.getTlf();
        String adr = kunde.getKundeAdresse();
        String by = kunde.getBy();
        int postnr = kunde.getPostNr();
    }

    public void deactivateKunde(Kunde kunde) {
        //ADD LOGIC?! c^^)
    }

    public void addOrdre(Ordre ordre, PizzaOrdre[] pizzaer) throws SQLException {
        int pizzaSize;
        int antal;
        int pizzaId;
        int ordreId;

        String sql = "INSERT INTO ordre (bestillingsDato, leveringsDato, udbringning, status, kundeId, budId) VALUES('" + ordre.getBestillingDatoTid() + "','" + ordre.getLeveringsDatoTid() + "','1','0','" + ordre.getKundeId() + "','1')";
        stmt.execute(sql);

        String sql1 = "SELECT ordreId FROM ordre ORDER BY ordreId DESC LIMIT 1";
        rs = stmt.executeQuery(sql1);

        rs.next();
        ordreId = rs.getInt("ordreId");

        for (int i = 0; i < pizzaer.length; i++) {
            PizzaOrdre pizzaOrdre = pizzaer[i];

            pizzaId = pizzaOrdre.getPizza().getPizzaId();
            antal = pizzaOrdre.getAntal();
            pizzaSize = pizzaOrdre.getSize();

            String sql2 = "INSERT INTO pizzaOrdre (pizzaId, ordreId, antal, pizzaSizeId) VALUES('" + pizzaId + "','" + ordreId + "','" + antal + "','" + pizzaSize + "')";

            stmt.execute(sql2);

        }
    }

    public Ordre getOrdre(int ordreID) {
        Ordre ordre = new Ordre();

        return ordre;
    }

    public void editOrdre(Ordre ordre) {
    }

    public void deleteOrdre(Ordre ordre) {
    }

    public ArrayList aktivOrdreList() throws SQLException {
        ArrayList aktivOrdreList = new ArrayList();

        // Find alle ordrene der er klar til udbrigning.
        String sql = "SELECT * from ordre WHERE ordre.status < 3";
        rs = stmt.executeQuery(sql);
        while (rs.next()) {

            // Opret ordreren.
            Ordre ordre = new Ordre(rs.getInt("ordreId"), rs.getInt("status"), rs.getInt("kundeId"));
            //add til liste
            aktivOrdreList.add(ordre);

        }
        return aktivOrdreList;
    }

    public ArrayList<Ordre> budOrdreList() throws SQLException {
        ArrayList<Ordre> budOrdreList = new ArrayList();

        // Find alle ordrene der er klar til udbrigning.
        String sql = "SELECT * from ordre WHERE ordre.status = 3";
        rs = stmt.executeQuery(sql);
        while (rs.next()) {

            // Opret ordreren.
            Ordre ordre = new Ordre(rs.getInt("ordreId"), rs.getInt("status"), rs.getInt("kundeId"));
            //add til liste
            budOrdreList.add(ordre);

        }
        return budOrdreList;
    }

    public ArrayList<Ordre> kundeOrdreList(int kundeId) throws SQLException {
        ArrayList<Ordre> kundeOrdreList = new ArrayList();

        // Find alle ordrene der hører til en kunde.
        String sql = "SELECT * from ordre WHERE ordre.kundeId = '" + kundeId + "'";
        rs = stmt.executeQuery(sql);
        while (rs.next()) {

            // Opret ordreren.
            Ordre ordre = new Ordre(rs.getInt("ordreId"), rs.getInt("status"), rs.getInt("kundeId"));
            //add til liste
            kundeOrdreList.add(ordre);
        }
        return kundeOrdreList;
    }

    public ArrayList<Topping> allToppingList() throws SQLException {
        ArrayList<Topping> allToppingList = new ArrayList();

        // Find alle ordrene der hører til en kunde.
        String sql = "SELECT * from topping";
        rs = stmt.executeQuery(sql);
        while (rs.next()) {

            // Opret ordreren.
            Topping topping = new Topping(rs.getString("navn"));
            //add til liste
            allToppingList.add(topping);

        }
        return allToppingList;
    }

    public void addBud(Bud bud) throws SQLException {
        String sql = "INSERT INTO bud (fornavn, efternavn) VALUES('" + bud.getFornavn() +"', '" + bud.getEfternavn() +"')";
        stmt.execute(sql);
        stmt.close();
    }

    public void editBud(Bud bud) throws SQLException {
        String sql = "UPDATE bud SET fornavn = '" + bud.getFornavn() + "' AND efternavn = '" + bud.getEfternavn() + "' WHERE budId = '" + bud.getId() + "'";
        stmt.execute(sql);
        stmt.close();
    }

    public void deleteBud(Bud bud) throws SQLException {
        String sql = "DELETE FROM bud WHERE budId = '" + bud.getId() + "'";
        stmt.execute(sql);
        stmt.close();
    }
    
   public void getBud(Bud bud) throws SQLException {
        String sql = "SELECT * from bud WHERE fornavn = '" + bud.getFornavn() + "' AND efternavn = '" + bud.getEfternavn() + "'";
        rs = stmt.executeQuery(sql);
        rs.next();
        bud.setId(rs.getInt("budid"));
    }
}